import re

import mysql.connector
import pandas as pd
from mysql.connector import Error

from util.common import read_sql_file

config = {
    'user': 'root',
    'password': '123456',
    'host': '127.0.0.1',
    'database': 'smp',
    'raise_on_warnings': True
}
connection = mysql.connector.connect(**config)

def trans_oracle_mysql(oracle_insert):
    # 定义正则表达式模式
    # to_date('2024-01-01 19:17:42', 'yyyy-mm-dd hh24:mi:ss')
    to_date_pattern = re.compile(r"to_date\('(\d{2}-\d{2}-\d{4} \d{2}:\d{2}:\d{2})', 'dd-mm-yyyy hh24:mi:ss'\)")
    to_date_pattern2 = re.compile(r"to_date\('(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})', 'yyyy-mm-dd hh24:mi:ss'\)")
    # 替换 to_date 函数
    mysql_insert = to_date_pattern.sub(r"STR_TO_DATE('\1', '%d-%m-%Y %H:%i:%s')", oracle_insert)
    mysql_insert = to_date_pattern2.sub(r"STR_TO_DATE('\1', '%Y-%m-%d %H:%i:%s')", mysql_insert)
    return mysql_insert

def insert_data(file_path):
    # 连接到 MySQL 数据库
    if connection.is_connected():
        print("Connected to the database")
        # 创建游标对象
        cursor = connection.cursor()
        # 执行插入语句
        for sql in read_sql_file(file_path, trans_oracle_mysql):
            cursor.execute(sql)
        # 提交事务
        connection.commit()
        print("Data inserted successfully")


def dataframe_from_db(sql):
    df = pd.read_sql(sql, connection)
    return df


if __name__ == '__main__':
    # insert_data('../source/app_check_basic_info.sql')
    # insert_data('/Users/lujiewen/asiainfo-sec/fujian-data/source/tf_sa_compliance_task20241129.sql')
    # insert_data('/Users/lujiewen/asiainfo-sec/fujian-data/source/tf_vu_hole_task20241129-2.sql')
    insert_data('/Users/lujiewen/asiainfo-sec/fujian-data/source/TL_LOG_USER20241129-3.sql')
